#include <stdio.h>
#include "sql_puc.h"
#include <stdlib.h>
#include "log_file.h"
#define HOST "localhost"
#define SQL_USER_NAME "root"
#define SQL_USER_PWD "asdf1234"
#define DB_NAME "myreview"
#define TABLE_STU "student"
#define TABLE_SUB "subject"
#define TABLE_ELE "elective"

MYSQL *conn_prt;

int init_sql(){
    conn_prt = mysql_init(NULL);
    if(!mysql_real_connect(conn_prt, HOST, SQL_USER_NAME,\
                           SQL_USER_PWD, DB_NAME, 0, NULL, 0)){
        return 0;
    }
    return 1;
}

int close_sql(){
    mysql_close(conn_prt);
    return 1;
}

MYSQL* query_sql(char* query){
    char buff[1024];
    sprintf(buff, "执行语句, %s", query);
    write_buff_log("sql",  buff, 1024);
    mysql_query(conn_prt, query);
    return conn_prt;
}

int query_sql_int(char* query){
    int res = mysql_query(conn_prt, query);
    char buff[1024];
    sprintf(buff, "执行语句, %s", query);
    write_buff_log("sql",  buff, 1024);
    if(res == 0){
        return 1;
    }
    return 0;
}

/**
 *获取表中所有数据
 *table 表名
 */
MYSQL* select_all_sql(char *table){
    char buff[200];
    sprintf(buff, "select * from %s", table);
    return query_sql(buff);
}

/**
 *获取学生表中学号为id的数据
 *id 学生学号
 * */
MYSQL* select_stu_id_sql(int id){
    char buff[200];
    sprintf(buff, "select * from %s where id = %d", TABLE_STU, id);
    return query_sql(buff);
}

/**
 *获取学生表中姓名为name的数据
 *name 学生姓名
 * */
MYSQL* select_stu_name_sql(char *name){
    char buff[200];
    sprintf(buff, "select * from %s where name = \'%s\'", TABLE_STU, name);
    return query_sql(buff);
}

/**
 *获取关联表中选择课程编号为id的学生
 *id 课程编号
 * */
MYSQL* select_stu_by_sub_sql(int id){
    char buff[200];
    sprintf(buff, "select * from %s where id in \
            (select stu_id from %s where sub_id = %d)", TABLE_STU, TABLE_ELE, id);
    return query_sql(buff);
}

/**
 * 获取学生表中所有的学生信息
 * */
MYSQL* select_stu_all_sql(){
    char buff[200];
    sprintf(buff, "select * from %s", TABLE_STU);
    return query_sql(buff);
}

/**
 *获取课程表中编号为id的课程数据
 *id 课程编号
 * */
MYSQL* select_sub_id_sql(int id){
    char buff[200];
    sprintf(buff, "select * from %s where id = %d", TABLE_SUB, id);
    return query_sql(buff);
}

/**
 *获取课程表中所有的课程数据
 *
 * */
MYSQL* select_sub_all_sql(){
    char buff[200];
    sprintf(buff, "select *  from %s", TABLE_SUB);
    return query_sql(buff);
}

/**
 *获取关联表中学号为id的学生所选择的所有课程
 *id 学生学号
 * */
MYSQL* select_sub_by_stu_sql(int id){
    char buff[200];
    sprintf(buff, "select * from %s where id in \
            (select sub_id from %s where stu_id = %d)", TABLE_SUB, TABLE_ELE, id);
        return query_sql(buff);
}

/**
 *获取关联表中学号为id的学生没有选择的所有课程
 *id 学生学号
 * */
MYSQL* slelect_sub_by_not_stu_sql(int id){
    char buff[200];
    sprintf(buff, "select * from %s where id not in \
            (select sub_id from %s where stu_id = %d)", TABLE_SUB, TABLE_ELE, id);
        return query_sql(buff);
}

/**
 *修改学生表中学号为id的学生的姓名
 *id 学生学号
 *name 学生姓名
 *成功返回1，失败返回0
 * */
int update_stu_sql(int id, char* name){
    char buff[200];
    sprintf(buff, "update %s set name = \'%s\' where id = %d", TABLE_STU, name, id);
    return query_sql_int(buff);
}

/**
 *修改课程表中编号为id的所有信息
 *id 课程编号
 *name nature period credit max_num 课程名 性质 课时 学分 学生最大容纳量
 *成功返回1，失败返回0
 * */
int update_sub_sql(int id, char* name, char* nature, int period, float credit, int max_num){
    char buff[200];
    sprintf(buff, "update %s set name = \'%s\', nature = \'%s\', per = %d,\
            credit = %f, max_num = %d where id = %d", \
            TABLE_SUB, name, nature, period, credit, max_num, id);
    return query_sql_int(buff);
}

/**
 *向学生表中插入学生信息
 *id 学生学号
 *name 学生姓名
 *成功返回1，失败返回0
 * */
int insert_stu_sql(int id, char* name){
    char buff[200];
    sprintf(buff, "insert into %s(id, name) values(%d, \'%s\')", TABLE_STU, id, name);
    return query_sql_int(buff);
}

/**
 *向课程表中插入课程信息
 *id name nature period credit max_num 课程编号 课程名 性质 课时 学分 学生最大容纳量
 *成功返回1，失败返回0
 * */
int insert_sub_sql(int id, char* name, char* nature, int period, float credit, int max_num){
    char buff[200];
    sprintf(buff, "insert into %s(id, name, nature, per, credit, max_num)\
            values(%d, \'%s\', \'%s\', %d, %f, %d)", TABLE_SUB, id, name, nature, period, credit, max_num);
    return query_sql_int(buff);
}

/**
 *向选课表中添加关联数据
 *stu_id 学生学号
 *sub_id 课程编号
 *成功返回1，失败返回0
 * */
int insert_stu_sub(int stu_id, int sub_id){
    char buff[200];
    sprintf(buff, "insert into %s(stu_id, sub_id) values(%d, %d)", TABLE_ELE, stu_id, sub_id);
    return query_sql_int(buff);
}

/**
 *删除id为id的学生数据或课程数据
 *table 表名
 *id 学生学号或课程编号
 *成功返回1，失败返回0
 * */
int delete_sql(char* table, int id){
    char buff[200];
    sprintf(buff, "delete from %s where id = %d", table, id);
    return query_sql_int(buff);
}

/**
 *删除学生表中学号为id的学生数据
 *id 学生学号
 *成功返回1，失败返回0
 * */
int delete_stu_sql(int id){
    char buff[200];
    sprintf(buff, "delete from %s where id = %d", TABLE_STU, id);
    return query_sql_int(buff);
}

/**
 *删除课程表中课程编号为id的课程数据
 *id 课程编号
 *成功返回1，失败返回0
 * */
int delete_sub_sql(int id){
    char buff[200];
    sprintf(buff, "delete from %s where id = %d", TABLE_SUB, id);
    return query_sql_int(buff); 
}

/**
 *删除选课表中的学号为stu_id的学生和编号为sub_id的课程关联
 *stu_id 学生学号
 *sub_id 课程编号
 *成功返回1，失败返回0
 * */
int delete_stu_sub(int stu_id, int sub_id){
    char buff[200];
    sprintf(buff, "delete from %s where stu_id = %d and sub_id = %d",\
            TABLE_ELE, stu_id, sub_id);
    return query_sql_int(buff);
}

/**
 *获取选课表中选课的课程编号为id的学生数量
 *id 课程编号
 *成功返回学生数量，失败返回0
 * */
int select_stu_num_sub(int id){
    char buff[200];
    sprintf(buff, "select count(*) from %s where sub_id = %d", TABLE_ELE, id);
    if(query_sql_int(buff) == 0){
        return 0;
    }
    MYSQL_RES *res = mysql_use_result(conn_prt);
    if(res == NULL){
        return 0;
    }
    MYSQL_ROW row = mysql_fetch_row(res);
    int count = atoi(row[0]);
    mysql_free_result(res);
    return count;
}

/**
 *获取选课表中学号为id的学生所选的课
 *id 学生学号
 *sum_credit 所选课程学分总和
 *成功返回选课数量，失败返回0
 * */
int select_sub_num_stu(int id, float* sum_credit){
    char buff[200];
    sprintf(buff, "select count(*), sum(credit) from subject where id in\
    (select sub_id from elective where stu_id = %d)", id);
    if(query_sql_int(buff) == 0){
        printf("Error making query:%s\n",mysql_error(conn_prt));
        return 0;
    }
    MYSQL_RES *res = mysql_use_result(conn_prt);
    if(res == NULL){
        return 0;
    }
    MYSQL_ROW row = mysql_fetch_row(res);
    if(row[1] != NULL){
        *sum_credit = atof(row[1]);
    }
    int count = atoi(row[0]);
    mysql_free_result(res);
    return count;
}
